package com.situ.staff.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

//import com.mysql.cj.result.Field;
//import com.sun.corba.se.impl.encoding.OSFCodeSetRegistry.Entry;
//import java.lang.reflect;
import java.lang.reflect.Field;
import java.sql.Statement;

public class JDBCUtil {
	private static ResourceBundle rb = ResourceBundle.getBundle("com.situ.staff.util.jdbc");
	private static String className = rb.getString("driver");
	private static String user = rb.getString("user");
	private static String password = rb.getString("password");
	private static String url = rb.getString("url");

	// 加载驱动
	static {
		try {
			Class.forName(className);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 获得连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, password);
	}

	/**
	 * 关闭连接
	 * 
	 * @param con
	 * @param st
	 * @param ps
	 * @throws SQLException
	 */
	public static void close(Connection con, Statement st) throws SQLException {
		if (con != null) {
			con.close();
			con = null;

		}
		if (st != null) {
			st.close();
			st = null;

		}

	}

	public static void close(Connection con, PreparedStatement ps, ResultSet rs) throws SQLException {
		if (con != null) {
			con.close();
			con = null;

		}
		if (ps != null) {
			ps.close();
			ps = null;

		}
		if (rs != null) {
			rs.close();
			rs = null;
		}
	}

	/**
	 * 执行DML语句
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public static Integer update(String sql, List<Object> vals) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(sql);
			for (int i = 0; i < vals.size(); i++) {
				ps.setObject(i + 1, vals.get(i));
			}
			return ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close(con, ps, null);
		}
		return null;
	}

	public static Integer update(String sql, Object... vals) throws SQLException {
		return update(sql, Arrays.asList(vals));
	}

	// 如果数组中元素是引用类型，那么asList方法返回的list元素就是数组中的元素
	// 数组中元素类型是基本数据类型，那么asList方法返回的元素仅有一个，即这个数组
	/**
	 * 执行DQL
	 * 
	 * @param <T>    返回集合中的元素类型
	 * @param sql
	 * @param vals   执行的sql的参数
	 * @param clazz  返回的集合中的元素类型的类的描述
	 * @param fields key =属性名 value =字段名
	 * @return
	 * @throws SQLException
	 */
	public static <T> List<T> query(String sql, List<Object> vals, Class<T> clazz, Map<String, String> fields)
			throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<T> result = new ArrayList<T>();
		try {
			con = getConnection();
			ps = con.prepareStatement(sql);
			for (int i = 0; i < vals.size(); i++) {
				ps.setObject(i + 1, vals.get(i));
			}
//			for (Map.Entry<String, String> entry : fields.entrySet()) {
//					System.out.println(entry.getKey()+" "+entry.getValue());
//			}
			rs = ps.executeQuery();
			while (rs.next()) {
				T model = clazz.newInstance();// 通过反射动态获取对象
				// 通过反射，给对象的属性赋值
				for (Map.Entry<String, String> entry : fields.entrySet()) {
					Field field = clazz.getDeclaredField(entry.getKey());// 通过反射获取对象的属性
//					System.out.println(field);
					field.setAccessible(true);
					field.set(model, rs.getObject(entry.getValue()));// 给属性赋值
				}
//				System.out.println(model.toString());
				result.add(model);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close(con, ps, rs);
		}
		return result;
	}

	public static <T> List<T> query(String sql, List<Object> vals, Class<T> clazz, List<String> props)
			throws SQLException {
		Map<String, String> fields = new HashMap<String, String>();
		for (String p : props) {
//			if(p.equals("codeDept")) {
//				fields.put("codeDept", "code_dept");
//			}else {
				fields.put(p, p);
//			}
		}
		return query(sql, vals, clazz, fields);
	}

	public static <T> List<T> query(String sql, List<Object> vals, Class<T> clazz) throws SQLException {
		Map<String, String> fields = new HashMap<String, String>();
		for (Field p : clazz.getDeclaredFields()) {
			fields.put(p.getName(),p.getName());
		}
		return query(sql, vals, clazz, fields);
	}
	
	public static <T> T queryModel(String sql, List<Object> vals, Class<T> clazz, Map<String, String> fields)
			throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(sql);
			for (int i = 0; i < vals.size(); i++) {
				ps.setObject(i + 1, vals.get(i));
			}
			rs = ps.executeQuery();
			if (rs.next()) {
				T model = clazz.newInstance();// 通过反射动态获取对象
				// 通过反射，给对象的属性赋值
				for (Map.Entry<String, String> entry : fields.entrySet()) {
					Field field = clazz.getDeclaredField(entry.getKey());// 通过反射获取对象的属性
					field.setAccessible(true);
					field.set(model, rs.getObject(entry.getValue()));// 给属性赋值
				}
				return model;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			close(con, ps, rs);
		}
		return null;
	}

	public static <T> T queryModel(String sql, List<Object> vals, Class<T> clazz, List<String> props)
			throws SQLException {
		Map<String, String> fields = new HashMap<String, String>();
		for (String p : props) {
			if("codeDept".equals(p)) {
				fields.put("codeDept","code_dept");
			}else {
				fields.put(p, p);
			}
		}
		return queryModel(sql, vals, clazz, fields);
	}

	public static <T> T queryModel(String sql, List<Object> vals, Class<T> clazz) throws SQLException {
		Map<String, String> fields = new HashMap<String, String>();
		for (Field p : clazz.getDeclaredFields()) {
			fields.put(p.getName(),p.getName());
		}
		return queryModel(sql, vals, clazz, fields);
	}
	
	
	public static Integer queryInt(String sql,List<Object> vals) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(sql);
			for (int i = 0; i < vals.size(); i++) {
				ps.setObject(i + 1, vals.get(i));
			}
			rs = ps.executeQuery();
			if(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
		
	}
	/**
	 * 构造方法私有化
	 */
	private JDBCUtil() {

	}

}
